CREATE TYPE majorstype AS VARRAY(2) OF varchar2(20);

CREATE TABLE Stu (
	stuId		VARCHAR2(6),
	lastName	VARCHAR2(20) NOT NULL,
	firstName 	VARCHAR2(20) NOT NULL,
	matriculated	varchar2(5) DEFAULT 'false',
	majors		majorstype,
	credits 	SMALLINT DEFAULT 0,
	CONSTRAINT Stu_Id_pk PRIMARY KEY(stuId));




INSERT INTO Stu VALUES('S555', 'Quirk','Sean', 'true', majorstype('French','Psychology'), 30);
INSERT INTO Stu VALUES('S511', 'Marks','Daniel', 'true', majorstype('History',null), 15);
INSERT INTO Stu VALUES('S599', 'White','Kimberly', 'true', majorstype('CSC','Math'), 60);



select s.*
from Stu s;


select s.stuid, s.majors
from Stu s;

-- the following returns an error, showing we cannot use subscripts to access an array

select s.stuid, s.majors(1) from Stu s;

-- we must use PL/SQL instead, with a loop for the subscripts 


set serveroutput on;

create or replace procedure printarray as
id		varchar2(6);
fname		varchar2(20);
lname		varchar2(20);
mymajors  	majorstype; 
cursor majcursor is
	select s.stuid, s.firstname, s.lastname, s.majors
	from Stu s;
begin
	open majcursor;
	dbms_output.put_line('Id '||'        First Name '||'        Last Name '||'     Majoring in');
	dbms_output.put_line('---------------------------------------------------------------------');
	loop
		fetch majcursor into id, fname, lname, mymajors;
		exit when majcursor%notfound;
		dbms_output.put(id||'       '||fname||'             '||lname); 
		for i in 1..2 loop
			if (mymajors(i)is not null) then
				dbms_output.put('          '||mymajors(i)); 
			end if;
		end loop; -- FOR MAJORS
	dbms_output.put_line(' ');
	end loop; -- FOR STUDENTS
	close majcursor;
end; 
/

